package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.TenpoRelationBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.TenpoRelationModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class TenpoRelationDao {
	private static TenpoRelationDao tenpoRelationDao = new TenpoRelationDao();

	/** ログ */
	private Log log = LogFactory.getLog(TenpoRelationDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static TenpoRelationDao getInstance(){
		return tenpoRelationDao;
	}

	private String SQL_TENPO_RELATION = " SELECT * FROM T_TENPO_RELATION WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている現金種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<TenpoRelationModel> getTenpoRelationInfo(Connection con, String pkGroupCod, String pkTenpoCod, String pkRelationKbn) throws Exception{
		List<TenpoRelationModel> resultList = new ArrayList<TenpoRelationModel>();

		//検索条件の作成
		String where = "";
		List<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		String whereInt = "";
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( BSWSUtils.isNotBlankNull(pkRelationKbn)){
			whereInt += " AND RELATION_KBN = ? ";
			whereIntList.add(Integer.parseInt(pkRelationKbn));
		}

		PreparedStatement stmt = con.prepareStatement(SQL_TENPO_RELATION + where + whereInt );
		stmt.setString(1, pkGroupCod);
		int seqCnt = 1;
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + seqCnt + 1, (String)whereList.get(i));
			seqCnt++;
		}
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + seqCnt + 1, (Integer)whereIntList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		TenpoRelationModel tenpoRelationModel = null;
		while(rs.next()){
			tenpoRelationModel = new TenpoRelationModel();
			tenpoRelationModel.setStrGroupCod(rs.getString("GROUP_COD"));
			tenpoRelationModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			tenpoRelationModel.setIntRelationKbn(rs.getInt("RELATION_KBN"));
			tenpoRelationModel.setIntSeq(rs.getInt("SEQ"));
			resultList.add(tenpoRelationModel);
		}
		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_TENPO_RELATION_INSERT =
		" INSERT INTO " +
		"     T_TENPO_RELATION ( " +
		"         GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,RELATION_KBN " +
		"        ,SEQ " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 店舗関連情報追加
	 */
	public int insertTenpoRelationAll(Connection con, BSWSSession bSWSSession , String strSansyoTenpoCod, TenpoRelationBean tenpoRelationBean) throws Exception{

		int cnt = 0;
		//来店要因情報登録
		List<String> tmpList = tenpoRelationBean.getRaitenYoinList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_RAITEN_YOIN, seq);
			}
		}

		//売上種別情報登録
		tmpList = tenpoRelationBean.getUriageShubetsuList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_URIAGE_SHUBETSU, seq);
			}
		}

		//支払方法情報登録
		tmpList = tenpoRelationBean.getShiharaiHohoList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_SHIHARAI_HOHO, seq);
			}
		}

		//報酬種別情報登録
		tmpList = tenpoRelationBean.getHosyuShubetsuList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_HOSYU_SHUBETSU, seq);
			}
		}

		//現金出納情報登録
		tmpList = tenpoRelationBean.getGenkinShutsunoList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_GENKIN_SHUTSUNO, seq);
			}
		}

		//消費税情報登録
		tmpList = tenpoRelationBean.getSyohizeiList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_SYOHIZEI, seq);
			}
		}

		//従業員情報登録
		tmpList = tenpoRelationBean.getJugyoinList();
		for(int i = 0; i < tmpList.size(); i++){
			int seq = Integer.parseInt(tmpList.get(i));
			if(seq >= 0){
				cnt += insertTenpoRelation(con, bSWSSession, strSansyoTenpoCod
						, BSWSConstants.KBN_JUGYOIN, seq);
			}
		}

		return cnt;
	}

	/**
	 * 店舗関連情報追加
	 */
	public int insertTenpoRelation(Connection con, BSWSSession bSWSSession, String strTenpoCod, int intRelationKbn, int intSeq) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_TENPO_RELATION_INSERT);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setString(2, strTenpoCod);
		stmt.setInt(3, intRelationKbn);
		stmt.setInt(4, intSeq);
		stmt.setString(5,  bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	/**
	 * 店舗関連情報削除
	 */
	public int deleteTenpoRelation(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{

		PreparedStatement stmt = con.prepareStatement(" DELETE FROM  T_TENPO_RELATION WHERE GROUP_COD = ? AND TENPO_COD = ? ");

		stmt.setString(1, pkGroupCod);
		stmt.setString(2, pkTenpoCod);

		int deleteCnt = stmt.executeUpdate();
		stmt.close();

		return deleteCnt;
	}

//	private String SQL_SHIHARAI_HOHO_UPDATE =
//		" UPDATE " +
//		"     M_SHIHARAI_HOHO " +
//		" SET " +
//		"         TENPO_COD = ? " +
//		"        ,SHIHARAI_HOHO_KNJ = ? " +
//		"        ,SHIHARAI_HOHO_RYAKU_KNJ = ? " +
//		"        ,SHIHARAI_HOHO_HYOJI_JUN = ? " +
//		"        ,SHIHARAI_HOHO_HYOJI_FLG = ? " +
//		"        ,HONTAI_FUKUMU_FLG = ? " +
//		"        ,KOSHIN_TMS = now() " +
//		"        ,KOSHINSHA_COD = ? " +
//		" WHERE " +
//		"     SEQ = ? ";

//	/**
//	 * 支払方法情報更新
//	 */
//	public int updateShiharaiHoho(Connection con,BSWSSession bSWSSession , ShiharaiHohoBean shiharaiHohoBean) throws Exception{
//
//		PreparedStatement stmt = con.prepareStatement(SQL_SHIHARAI_HOHO_UPDATE);
//
//		stmt.setString(1, shiharaiHohoBean.getStrTenpoCod());
//		stmt.setString(2, shiharaiHohoBean.getStrShiharaiHohoKnj());
//		stmt.setString(3, shiharaiHohoBean.getStrShiharaiHohoRyakuKnj());
//		stmt.setInt(4, Integer.parseInt(shiharaiHohoBean.getStrShiharaiHohoHyojiJun()));
//		stmt.setString(5, shiharaiHohoBean.getStrShiharaiHohoHyojiFlg());
//		stmt.setString(6, shiharaiHohoBean.getStrShiharaiHohoHontaiFukumuFlg());
//		stmt.setString(7, bSWSSession.getStrJugyoinCod());
//		stmt.setInt(8, shiharaiHohoBean.getSeq());
//
//		int cnt = stmt.executeUpdate();
//
//		stmt.close();
//
//		return cnt;
//	}

	private String SQL_KV = " SELECT SEQ,SHIHARAI_HOHO_KNJ FROM M_SHIHARAI_HOHO WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている支払方法情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getShiharaiHohoList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KV + where);

		stmt.setString(1, pkGroupCod);
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			stmt.setString(2, pkTenpoCod);
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("SEQ");
			String value = rs.getString("SHIHARAI_HOHO_KNJ");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}

	public String makeSeqList(List<TenpoRelationModel> tmpList) throws Exception{
		String result = "";
		if(tmpList.size() == 0){
			return " AND SEQ IS NULL ";
		}

		for(int i = 0; i < tmpList.size(); i++) {
			TenpoRelationModel tenpoRelationModel = tmpList.get(i);
			if( i == 0 ){
				result += " AND SEQ IN ( ";
			}
			result += tenpoRelationModel.getIntSeq();
			if( i == tmpList.size() - 1 ){
				result += " ) ";
			}else{
				result += " , ";
			}
		}
		return result;
	}
}